import logging
from typing import Optional

from fastapi import APIRouter, Query, Depends
from pydantic import Field, BaseModel
from sqlalchemy import text
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import Session

from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db
from utils.EncryptionUtil import oauth2_scheme, EncryptionMiddleware

data_ts_pack = APIRouter(prefix="/container/tsPack", tags=["危化品管理"])


class PackSearch(PaginationBase):
    # 筛选条件
    fsiteNo: str = Field("", description="站点编号")
    fpackno: str = Field("", description="包装编号")
    fstorageflag: str = Field("", description="存储标识")
    fcategory: str = Field("", description="类别")


@data_ts_pack.get("/list", summary="危化品容器接口")
def list_ts_pack(pack: PackSearch = Query(), db: Session = Depends(get_db)):
    param = {}
    sql = "select count(1) from i_ts_pack where 1=1"
    if pack.fsiteNo != "":
        sql = sql + " and fsite_no = :site_no "
        param["site_no"] = pack.fsiteNo
    if pack.fpackno != "":
        sql = sql + " and fpackno like concat('%', :fpackno ,'%')"
        param["fpackno"] = pack.fpackno
    if pack.fstorageflag != "":
        sql = sql + " and fstorageflag = :fstorageflag "
        param["fstorageflag"] = pack.fstorageflag
    if pack.fcategory != "":
        sql = sql + " and fcategory = :fcategory "
        param["fcategory"] = pack.fcategory
    count = db.execute(text(sql), param).scalar()

    param = {}
    sql = "select fsite_no,fpackno,fpackname,fgoodno,fbarcode,"
    sql = sql + " fengname,fclassstno,fspec,funit,fbuyprice,"
    sql = sql + " fstorageflag,fcolor,fweight,fvolume,fupddate,"
    sql = sql + " foperator,fcategory"
    sql = sql + " from i_ts_pack where 1=1"
    if pack.fsiteNo != "":
        sql = sql + " and fsite_no = :site_no "
        param["site_no"] = pack.fsiteNo
    if pack.fpackno != "":
        sql = sql + " and fpackno like concat('%', :fpackno ,'%')"
        param["fpackno"] = pack.fpackno
    if pack.fstorageflag != "":
        sql = sql + " and fstorageflag = :fstorageflag "
        param["fstorageflag"] = pack.fstorageflag
    if pack.fcategory != "":
        sql = sql + " and fcategory = :fcategory "
        param["fcategory"] = pack.fcategory
    sql = sql + " limit :limit offset :offset "
    param["offset"] = pack.offset
    param["limit"] = pack.limit
    data = db.execute(text(sql), param).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fsiteno'] = data[key][0]
        d1['fpackno'] = data[key][1]
        d1['fpackname'] = data[key][2]
        d1['fgoodno'] = data[key][3]
        d1['fbarcode'] = data[key][4]
        d1['fengname'] = data[key][5]
        d1['fclassstno'] = data[key][6]
        d1['fspec'] = data[key][7]
        d1['funit'] = data[key][8]
        d1['fbuyprice'] = data[key][9]
        d1['fstorageflag'] = data[key][10]
        d1['fcolor'] = data[key][11]
        d1['fweight'] = data[key][12]
        d1['fvolume'] = data[key][13]
        d1['fupddate'] = data[key][14]
        d1['foperator'] = data[key][15]
        d1['fcategory'] = data[key][16]
        data_list.append(d1)

    info_dict = {"list": data_list,
                 "limit": pack.limit,
                 "page": pack.pageIndex,
                 "pages": (count + pack.limit - 1) // pack.limit,
                 "total": count}
    return BaseResponse.success(data=info_dict)


class ContainerInfoCreate(BaseModel):
    """容器资料新增请求"""
    fpackno: str = Field(..., description="包装编号")
    fsiteNo: str = Field(..., description="站点编号")
    fpackname: str = Field(..., description="包装名称")
    fbarcode: Optional[str] = Field("", description="条形码，默认空字符串")
    fengname: Optional[str] = Field("", description="英文名，默认空字符串")
    fclassstno: Optional[str] = Field("", description="分类编号，默认空字符串")
    fspec: Optional[str] = Field("", description="规格，默认空字符串")
    funit: Optional[str] = Field("", description="单位，默认空字符串")
    fbuyprice: Optional[float] = Field(None, description="购买价格")
    fstorageflag: Optional[str] = Field("0", description="存储标识，默认'0'")
    fcolor: Optional[str] = Field("", description="颜色，默认空字符串")
    fweight: Optional[str] = Field("0", description="重量，默认'0'")
    fvolume: Optional[float] = Field(None, description="体积")
    fcategory: Optional[str] = Field("0", description="类别，默认'0'")


@data_ts_pack.post("/create", summary="容器资料新增接口")
def create_ts_pack(container: ContainerInfoCreate, db: Session = Depends(get_db), token: str = Depends(oauth2_scheme)):
    try:
        user_id = EncryptionMiddleware.get_current_user(token)
        with db.begin():
            sql = "select fsite_No from i_ts_pack where fpackno= :fpackno  and fsite_No= :fsiteNo"
            data = db.execute(text(sql), {
                "fpackno": container.fpackno,
                "fsiteNo": container.fsiteNo,
            }).fetchall()
            if len(data) > 0:
                return BaseResponse.error(message="容器编号已存在")
            else:
                sql = "insert into i_ts_pack(fsite_no,fpackno,fpackname,fbarcode,fengname,fclassstno,fspec,funit,fstorageflag,fcolor,fweight,fcategory,fupddate,foperator"
                if container.fbuyprice is not None:
                    sql = sql + ",fbuyprice"
                if container.fvolume is not None:
                    sql = sql + ",fvolume"
                sql = sql + ")"
                sql = sql + " values( :fsiteNo , :fpackno , :fpackname , :fbarcode , :fengname , :fclassstno , :fspec , :funit , :fstorageflag , :fcolor , :fweight , :fcategory ,now(), :userid "
                if container.fbuyprice is not None:
                    sql = sql + ", :fbuyprice "
                if container.fvolume is not None:
                    sql = sql + ", :fvolume "
                sql = sql + ");"
                db.execute(text(sql), {
                    "fsiteNo": container.fsiteNo,
                    "fpackno": container.fpackno,
                    "fpackname": container.fpackname,
                    "fbarcode": container.fbarcode,
                    "fengname": container.fengname,
                    "fclassstno": container.fclassstno,
                    "fspec": container.fspec,
                    "funit": container.funit,
                    "fstorageflag": container.fstorageflag,
                    "fcolor": container.fcolor,
                    "fweight": container.fweight,
                    "fcategory": container.fcategory,
                    "userid": user_id,
                    "fbuyprice": container.fbuyprice,
                    "fvolume": container.fvolume,
                })

                # 返回
                d1 = {}
                d1['fsiteno'] = container.fsiteNo
                d1['fpackno'] = container.fpackno
                d1['fpackname'] = container.fpackname
                d1['fgoodno'] = None
                d1['fbarcode'] = container.fbarcode
                d1['fengname'] = container.fengname
                d1['fclassstno'] = container.fclassstno
                d1['fspec'] = container.fspec
                d1['funit'] = container.funit
                d1['fbuyprice'] = container.fbuyprice
                d1['fstorageflag'] = container.fstorageflag
                d1['fcolor'] = container.fcolor
                d1['fweight'] = container.fweight
                d1['fvolume'] = container.fvolume
                d1['fupddate'] = None
                d1['foperator'] = user_id
                d1['fcategory'] = container.fcategory
                return BaseResponse.success(data=d1)
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@data_ts_pack.post("/update", summary="容器资料修改接口")
def update_ts_pack(container: ContainerInfoCreate, db: Session = Depends(get_db), token: str = Depends(oauth2_scheme)):
    try:
        user_id = EncryptionMiddleware.get_current_user(token)
        with db.begin():
            sql = "update i_ts_pack"
            sql = sql + " set fpackname= :fpackname ,fbarcode= :fbarcode "
            sql = sql + ",fengname= :fengname ,fclassstno= :fclassstno ,fspec= :fspec "
            sql = sql + ",funit= :funit ,fstorageflag= :fstorageflag "
            sql = sql + ",fcolor= :fcolor ,fweight= :fweight "
            sql = sql + ",fcategory= :fcategory ,fupddate=now(),foperator= :userid "
            if container.fbuyprice is not None:
                sql = sql + ",fbuyprice= :fbuyprice "
            if container.fvolume is not None:
                sql = sql + ",fvolume= :fvolume "
            sql = sql + " where fpackno= :fpackno and fsite_no= :fsiteNo "
            db.execute(text(sql), {
                "fpackname": container.fpackname,
                "fbarcode": container.fbarcode,
                "fengname": container.fengname,
                "fclassstno": container.fclassstno,
                "fspec": container.fspec,
                "funit": container.funit,
                "fstorageflag": container.fstorageflag,
                "fcolor": container.fcolor,
                "fweight": container.fweight,
                "fcategory": container.fcategory,
                "userid": user_id,
                "fbuyprice": container.fbuyprice,
                "fvolume": container.fvolume,
                "fpackno": container.fpackno,
                "fsiteNo": container.fsiteNo,
            })
            return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


class ContainerDelete(BaseModel):
    fsiteNo: str = Field(None, description="客户编号")
    fpackno: str = Field(None, description="容器编号")


@data_ts_pack.post("/delete", summary="容器删除接口")
def delete_ts_pack(container: ContainerDelete, db: Session = Depends(get_db)):
    try:
        with db.begin():
            sql = " select fpackno from i_pack_inv where fpackno= :fpackno  limit 3"
            data = db.execute(text(sql), {
                "fpackno": container.fpackno,
            }).fetchall()

            if len(data) > 0:
                return BaseResponse.error(message="该资料在使用，不能删除")
            else:
                sql = "delete from i_ts_pack where fpackno= :fpackno and fsite_no= :fsiteNo "
                db.execute(text(sql), {
                    "fpackno": container.fpackno,
                    "fsiteNo": container.fsiteNo,
                })
                return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")
